import os
import pandas as pd
import json
import glob

def process_excel_file(excel_file_path):
    # 获取文件名（不含扩展名）
    file_name = os.path.splitext(os.path.basename(excel_file_path))[0]
    
    # 读取Excel文件
    try:
        # 先读取Excel文件，查看第一行是否为中文列名，第二行是否为英文列名
        df_check = pd.read_excel(excel_file_path, header=None, nrows=2)
        # 检查第二行是否包含关键列名
        second_row = df_check.iloc[1].tolist()
        if 'orderid' in second_row and ('values' in second_row or 'missCard' in second_row):
            # 如果第二行包含关键列名，则使用第二行作为列名
            df = pd.read_excel(excel_file_path, header=1)
            print(f"使用第二行作为列名读取Excel文件: {excel_file_path}")
        else:
            # 否则使用第一行作为列名
            df = pd.read_excel(excel_file_path, header=0)
            print(f"使用第一行作为列名读取Excel文件: {excel_file_path}")
        print(f"成功读取Excel文件: {excel_file_path}")
    except Exception as e:
        print(f"读取Excel文件失败: {e}")
        return
    
    # 生成pool文件
    pool_data = generate_pool_file(df, file_name)
    
    # 生成orders文件
    generate_orders_file(df, file_name, pool_data)

def generate_pool_file(df, file_name):
    pool_data = []
    uid_counter = 1001  # 从1001开始为每张牌分配唯一ID
    
    # 处理missCard列，按照','分隔提取每张牌的牌值
    for index, row in df.iterrows():
        if 'missCard' in row and pd.notna(row['missCard']):
            miss_cards = str(row['missCard']).split(',')
            orderid = row['orderid'] if 'orderid' in row else index + 1
            
            for card_value in miss_cards:
                if card_value.strip():  # 确保不是空字符串
                    try:
                        value = int(card_value.strip())
                        pool_data.append({
                            "uid": uid_counter,
                            "orderid": orderid,
                            "value": value
                        })
                        uid_counter += 1
                    except ValueError:
                        print(f"警告: 无法将 '{card_value}' 转换为整数")
    
    # 保存为JSON文件
    pool_file_path = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'res', f"{file_name}_pool_new.json")
    with open(pool_file_path, 'w', encoding='utf-8') as f:
        json.dump(pool_data, f, indent=4, ensure_ascii=False)
    
    print(f"成功生成pool文件: {pool_file_path}")
    return pool_data

def generate_orders_file(df, file_name, pool_data):
    # 创建uid到value的映射
    uid_to_value = {item['uid']: item for item in pool_data}
    
    # 按orderid对pool_data进行分组，方便后续按顺序获取
    pool_by_orderid = {}
    for item in pool_data:
        orderid = item['orderid']
        if orderid not in pool_by_orderid:
            pool_by_orderid[orderid] = []
        pool_by_orderid[orderid].append(item)
    
    orders_data = {
        "orders": [],
        "sequence": []
    }
    
    # 提取sequence列数据
    if 'sequence' in df.columns:
        sequence_values = df['sequence'].dropna().tolist()
        orders_data["sequence"] = sequence_values
    
    # 处理orders数据
    for index, row in df.iterrows():
        # 跳过sequence行
        if pd.isna(row.get('values', None)):
            continue
        name = row.get('name', "")
        order = {
            "uid": str(index + 1),  # 订单在表格中的顺序
            "values": [],
            "suitCheck": name.count('同花') > 0,
            "name": name
        }
        
        # 处理values列，按照','分隔
        if 'values' in row and pd.notna(row['values']):
            values = str(row['values']).split(',')
            # 获取当前行的orderid
            current_orderid = row.get('orderid', index + 1)
            # 获取该orderid对应的所有pool项
            pool_items = pool_by_orderid.get(current_orderid, [])
            # 记录已使用的pool项索引
            #used_pool_indices = []
            
            missCardList = str(row['missCard']).split(',')
            
            index = 0
            for val in values:
                if val.strip():  # 确保不是空字符串
                    try:
                        value = int(val.strip())
                        # 如果值为-1，则需要从pool中获取对应的uid
                        if value == -1:
                            # 查找下一个未使用的pool项
                            missCard = missCardList[index]
                            
                            for i, pool_item in enumerate(pool_items):
                                if pool_item['orderid'] == current_orderid and pool_item['value'] == int(missCard):
                                    order["values"].append(pool_item['uid'])
                                    #used_pool_indices.append(i)
                                    break

                            index += 1

                        else:
                            order["values"].append(value)
                    except ValueError:
                        print(f"警告: 无法将 '{val}' 转换为整数")
        
        orders_data["orders"].append(order)
    
    # 保存为JSON文件
    orders_file_path = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'res', f"{file_name}_orders_new.json")
    with open(orders_file_path, 'w', encoding='utf-8') as f:
        json.dump(orders_data, f, indent=4, ensure_ascii=False)
    
    print(f"成功生成orders文件: {orders_file_path}")

def main():
    # 获取res目录下所有Excel文件
    excel_dir = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'res')
    excel_files = glob.glob(os.path.join(excel_dir, '*.xlsx'))
    
    if not excel_files:
        print(f"在 {excel_dir} 目录下未找到Excel文件")
        return
    
    # 处理每个Excel文件
    for excel_file in excel_files:
        # 跳过临时文件（以~$开头的文件）
        if os.path.basename(excel_file).startswith('~$'):
            continue
        process_excel_file(excel_file)

if __name__ == "__main__":
    main()